<?php
 
/*
 * Following code will list all the place
 */
 
// array for JSON response
$response = array();
 
if (isset($_GET['province_id'])){
	$province_id = $_GET['province_id'];
	
	// include db connect class
	require_once __DIR__ . '/db_connect.php';
	 
	// connecting to db
	$db = new DB_CONNECT();
	 
	// get all place from place table

	$result = mysql_query("SELECT pl.place_id
								,pl.place_name
								,pl.address
								,case
									when lk.count_like is null or lk.count_like = '' then 0
									else lk.count_like
								end count_like
								,case
									when cm.count_comment is null or cm.count_comment = '' then 0
									else cm.count_comment
								end count_comment
								,case
									when rt.point_rating is null or rt.point_rating = '' then 0
									else rt.point_rating
								end point_rating
								,case
									when rp.count_report is null or rp.count_report = '' then 0
									else rp.count_report
								end count_report
								,pl.url_avatar
								,pl.name_avatar
							FROM place as pl 
								LEFT JOIN  
									(select place_id, FLOOR(sum(point)/count(id)) as point_rating
									 from  rating 
									 group by place_id) as rt
									on rt.place_id = pl.place_id
								LEFT JOIN
									(select place_id, count(id) as count_like
									 from  `like` 
									 group by place_id) as lk
									on lk.place_id = pl.place_id
								LEFT JOIN
									(select place_id, count(comment_id) as count_comment
									 from  `comment` 
									 where `type` = 1
									 group by place_id) as cm
									on cm.place_id = pl.place_id
								LEFT JOIN
									(select place_id, count(id) as count_report
									 from  `report` 
									 group by place_id) as rp
									on rp.place_id = pl.place_id	
								LEFT JOIN district dt
									ON pl.district_id = dt.id
							WHERE dt.province_id = '$province_id' AND  IFNULL(count_report,0) < 10
							ORDER BY count_report ASC
									,point_rating DESC
									,count_like DESC
									,count_comment DESC
									,pl.place_name DESC") or die(mysql_error());
	 
	// check for empty result
	if (mysql_num_rows($result) > 0) {
		// looping through all results
		// place node
		$response["place"] = array();
	 
		while ($row = mysql_fetch_array($result)) {
			// temp user array
			$place = array();    
		$place["place_id"] = $row['place_id'];
		$place["place_name"] = $row['place_name'];
		$place["address"] = $row['address'];
		$place["count_like"] = $row['count_like'];
		$place["count_comment"] = $row['count_comment'];
		$place["point_rating"] = $row['point_rating'];	 
		$place["url_avatar"] = $row['url_avatar'];	
		$place["name_avatar"] = $row['name_avatar'];	
		
			// push single product into final response array
			array_push($response["place"], $place);
		}
		// success
		$response["success"] = 1;
	 
		// echoing JSON response
		echo json_encode($response);
	} else {
		// no place found
		$response["success"] = 0;
		$response["message"] = "No place found";
	 
		// echo no users JSON
		echo json_encode($response);
	}
} else {
    // required field is missing
    $response["success"] = 0;
    $response["message"] = "Required field(s) is missing";
 
    // echoing JSON response
    echo json_encode($response);
}

?>